/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package utilities;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author jiaf
 */
public class DBConnector {
    private Connection con = null;
    private Statement st = null;
    private ResultSet rs = null;
    private final String url;
    private final String database;
    private final String username;
    private final String password;
    
    public DBConnector(String url, String username, String password){
        this.url = url;
        this.database = url.split("/")[3];
        this.username = username;
        this.password = password;
    }
    
    public String getUsername(){
        return this.username;
    }
    
    public String getPassword(){
        return this.password;
    }
    
    public String getDatabase(){
        return this.database;
    }
    
    public boolean connect(){
        try {
            this.con = DriverManager.getConnection(url, username, password);
            st = con.createStatement();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(
                    DBConnector.class.getName()).log(Level.SEVERE,
                            null, ex);
        }
        return false;
    }
    
    public void disconnect(){
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(
                    DBConnector.class.getName()).log(Level.SEVERE,
                            null, ex);
        }
    }
    
    public void create(String table, String values){
        try {
            st.execute(String.format("CREATE TABLE %s (%s)", table, values));
        } catch (SQLException ex) {
            Logger.getLogger(
                    DBConnector.class.getName()).log(Level.SEVERE,
                            null, ex);
        }
    }
    
    public boolean check(String table){
        try {
            rs = con.getMetaData().getTables(null, null, table,
                    new String[] {"TABLE"});
            if (rs.next()){
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(
                    DBConnector.class.getName()).log(Level.SEVERE,
                            null, ex);
        }
        return false;
    }
    
    public boolean check(String table, String condition){
        try {
            rs = con.getMetaData().getTables(null, null, null,
                    new String[] {"TABLE"});
            if (st != null){
                String sql = String.format("SELECT * FROM %s WHERE %s", 
                        table, condition);
                rs = st.executeQuery(sql);
                if (rs.next()){
                    return true;
                }
            }
            else{
                System.err.println(
                        "Cannot execute SQL commands, statement is NULL.");
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(
                    DBConnector.class.getName()).log(Level.SEVERE,
                            null, ex);
        }
        return false;
    }
    
    public void insert(String record, String table){
        try {
            if (st != null){
                String sql = String.format("INSERT INTO %s VALUES (%s)",
                        table, record);
                st.execute(sql);
            }
            else{
                System.err.println(
                        "Cannot execute SQL commands, statement is NULL.");
            }
        } catch (SQLException ex) {
            Logger.getLogger(
                    DBConnector.class.getName()).log(Level.SEVERE,
                            null, ex);
        }
    }
    
    public void delete(String table, String condition){
        try {
            if (st != null){
                String sql = String.format("DELETE FROM %s WHERE %s", 
                        table, condition);
                st.execute(sql);
            }
        } catch (SQLException ex) {
            Logger.getLogger(
                    DBConnector.class.getName()).log(Level.SEVERE,
                            null, ex);
        }
    }
    
    public ResultSet load(String table){
        try {
            rs = con.getMetaData().getTables(null, null, null,
                    new String[] {"TABLE"});
            if (st != null){
                String sql = String.format("SELECT * FROM %s", table);
                return st.executeQuery(sql);
            }
        } catch (SQLException ex){
            Logger.getLogger(
                    DBConnector.class.getName()).log(Level.SEVERE,
                            null, ex);
        }
        return null;
    }
    
    public ResultSet load(String table, String column){
        try {
            rs = con.getMetaData().getTables(null, null, null,
                    new String[] {"TABLE"});
            if (st != null){
                String sql = String.format("SELECT %s FROM %s", column, table);
                return st.executeQuery(sql);
            }
        } catch (SQLException ex){
            Logger.getLogger(
                    DBConnector.class.getName()).log(Level.SEVERE,
                            null, ex);
        }
        return null;
    }
    
    public String load(String table, String condition, String key){
        try {
            rs = con.getMetaData().getTables(null, null, null,
                    new String[] {"TABLE"});
            if (st != null){
                String sql = 
                        String.format("SELECT * FROM %s WHERE %s",
                                table, condition);
                rs = st.executeQuery(sql);
                while (rs.next()){
                    return rs.getString(key);
                }
            }
        } catch (SQLException ex){
            Logger.getLogger(
                    DBConnector.class.getName()).log(Level.SEVERE,
                            null, ex);
        }
        return null;
    }
    
    public ResultSet search(String table, String key, String searchKey){
        try {
            rs = con.getMetaData().getTables(null, null, null,
                    new String[] {"TABLE"});
            if (st != null){
                String sql =
                        String.format("SELECT * FROM %s WHERE %s LIKE '%%%s%%'",
                                table, key, searchKey);
                return st.executeQuery(sql);
            }
        } catch (SQLException ex){
            Logger.getLogger(
                    DBConnector.class.getName()).log(Level.SEVERE,
                            null, ex);
        }
        return null;
    }
    
    public void update(String table, String set, String condition){
        try {
            if (st != null){
                String sql = String.format(
                        "UPDATE %s SET %s WHERE %s",
                        table, set, condition);
                st.execute(sql);
            }
        } catch (SQLException ex) {
            Logger.getLogger(
                    DBConnector.class.getName()).log(Level.SEVERE,
                            null, ex);
        }
    }
}
